Count all the Species

Question:

Start Quiz:

#
# Write a query that returns all the species in the zoo, and how many animals of
# each species there are, sorted with the most populous species at the top.
# 
# The result should have two columns:  species and number.
#
# The animals table has columns (name, species, birthdate) for each individual.
# 
QUERY = "select ..."

User's Answer:

(Note: The answer done by the user is not guaranteed to be correct)

query.py

#
# Write a query that returns all the species in the zoo, and how many animals of
# each species there are, sorted with the most populous species at the top.
# 
# The result should have two columns:  species and number.
#
# The animals table has columns (name, species, birthdate) for each individual.
# 
QUERY = "select species from animals group by species sum"

Solution:

INSTRUCTOR NOTE:

Note: for correct answer, order of columns in your query table should be species, number

Select clauses

These are all the select clauses we've seen in the lesson so far.

where

The where clause expresses restrictions — filtering a table for rows that follow a particular rule. where supports equalities, inequalities, and boolean operators (among other things):

where species = 'gorilla' — return only rows that have 'gorilla' as the value of the species column.
where name >= 'George' — return only rows where the name column is alphabetically after 'George'.
where species != 'gorilla' and name != 'George' — return only rows where species isn't 'gorilla' and name isn't 'George'.

limit / offset

The limit clause sets a limit on how many rows to return in the result table. The optional offset clause says how far to skip ahead into the results. So limit 10 offset 100 will return 10 results starting with the 101st.

order by

The order by clause tells the database how to sort the results — usually according to one or more columns. So order by species, name says to sort results first by the species column, then by name within each species.

Ordering happens before limit/offset, so you can use them together to extract pages of alphabetized results. (Think of the pages of a dictionary.)

The optional desc modifier tells the database to order results in descending order — for instance from large numbers to small ones, or from Z to A.

group by

The group by clause is only used with aggregations, such as max or sum. Without a group by clause, a select statement with an aggregation will aggregate over the whole selected table(s), returning only one row. With a group by clause, it will return one row for each distinct value of the column or expression in the group by clause.